﻿using Exiled.API.Features;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RgCore.Database
{
    //运行优先级： Query > Insert > Update > Delete
    public enum RequestType
    {
        Insert, Delete, Query, Update
    }

    public class CustomCommandRequest
    {
        public CustomCommandRequest(string command, params string[] columns)
        {
            Command = command;
            Columns = columns;
        }

        public string Command { get; }
        public string[] Columns { get; }

        public async Task<List<Dictionary<string, object>>> GetResponseResult()
        {
            var con = await DatabaseController.Rent();
            List<Dictionary<string, object>> result = await InternalGetResponseResult(con);
            DatabaseController.Return(con);
            return result;
        }

        private async Task<List<Dictionary<string, object>>> InternalGetResponseResult(DBConnection con)
        {
            return await Task.Run(() =>
            {
                try
                {
                    List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
                    MySqlCommand cmd = new MySqlCommand(Command, con.Connection);
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Dictionary<string, object> set = new Dictionary<string, object>();
                            foreach (var col in Columns)
                            {
                                set.Add(col, reader.GetValue(reader.GetOrdinal(col)));
                            }
                            result.Add(set);
                        }
                        reader.Close();
                    }
                    cmd.Dispose();
                    return result;
                }
                catch (Exception e)
                {
                    if (e.InnerException != null)
                    {
                        if (e.InnerException is IOException)
                        {
                            Log.Warn("执行查询类数据库请求捕获已知异常：与数据库的连接意外断开\n将重新建立连接并重新查询");
                            con.Connection.Close();
                            con.Connection.Open();
                            return null;
                        }
                    }
                    string text = $"执行查询数据库请求时出现错误：\n";
                    if (e.InnerException != null)
                    {
                        text += $"(内部) " + e.InnerException;
                    }
                    else
                    {
                        text += e.ToString();
                    }
                    text += $"\n语句：{Command}";
                    Log.Error(text);
                    return new List<Dictionary<string, object>>();
                }
            });
        }
    }

    //无回复请求，用于Insert/Update/Delete
    public class NoResponseCommandRequest : CommandRequest
    {
        public NoResponseCommandRequest(string table, RequestType type, Dictionary<string, object> keys, Dictionary<string, object> filter) : base(table, type, keys, filter)
        {
        }

        //返回类型为true：废除命令 | false：重新尝试执行命令
        public async Task ProcessNonQuery()
        {
            var con = await DatabaseController.Rent();
            string command = "UNDEFINED";
            try
            {
                switch (RequestType)
                {
                    case RequestType.Insert:
                        {
                            if (Keys == null)
                                break;
                            command = $"INSERT INTO `{Table}` (%KEYS%) VALUES (%VALUES%)";
                            string key = string.Empty;
                            string value = string.Empty;
                            foreach (var line in Keys)
                            {
                                key += $"`{line.Key}`, ";
                                value += $"{(line.Value is bool b ? (b ? "1" : "0") : $"'{line.Value.ToString().Replace("'", "\\'")}'")}, ";
                            }
                            key = key.Remove(key.Length - 2);
                            value = value.Remove(value.Length - 2);
                            command = command.Replace("%KEYS%", key).Replace("%VALUES%", value);
                            MySqlCommand cmd = new MySqlCommand(command, con.Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            break;
                        }
                    case RequestType.Delete:
                        {
                            if (Filter == null)
                                break;
                            command = $"DELETE FROM `{Table}`";
                            string filter = string.Empty;
                            foreach (var line in Filter)
                            {
                                filter += $"`{line.Key}` = {(line.Value is bool b ? (b ? "1" : "0") : $"'{line.Value.ToString().Replace("'", "\\'")}'")} AND ";
                            }
                            filter = filter.Remove(filter.Length - 4);
                            if (filter != String.Empty)
                                command += $" WHERE {filter}";
                            MySqlCommand cmd = new MySqlCommand(command, con.Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            break;
                        }
                    case RequestType.Update:
                        {
                            if (Keys == null)
                                break;
                            command = $"UPDATE `{Table}` SET ";
                            string key = string.Empty;
                            foreach (var line in Keys)
                            {
                                key += $"`{line.Key}` = {(line.Value is bool b ? (b ? "1" : "0") : $"'{line.Value.ToString().Replace("'", "\\'")}'")}, ";
                            }
                            key = key.Remove(key.Length - 2);
                            command += key;
                            if (Filter != null && Filter.Count > 0)
                            {
                                string filter = string.Empty;
                                foreach (var line in Filter)
                                {
                                    filter += $"`{line.Key}` = {(line.Value is bool b ? (b ? "1" : "0") : $"'{line.Value.ToString().Replace("'", "\\'")}'")} AND ";
                                }
                                filter = filter.Remove(filter.Length - 4);
                                command += $" WHERE {filter}";
                            }
                            MySqlCommand cmd = new MySqlCommand(command, con.Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            break;
                        }
                }
                return;
            }
            catch (Exception e)
            {
                if (e is MySqlException && e.Message.Contains("Duplicate"))
                {
                    Log.Warn("执行非查询类数据库请求捕获已知异常：插入了一条重复键\n此条命令将被废弃");
                    return;
                }
                if (e.InnerException != null)
                {
                    if (e.InnerException is IOException)
                    {
                        Log.Warn("执行非查询类数据库请求捕获已知异常：与数据库的连接意外断开\n将重新建立连接并重新执行此命令");
                        con.Connection.Close();
                        con.Connection.Open();
                        return;
                    }
                }
                string text = $"执行非查询类数据库请求时出现错误：\n";
                if (e.InnerException != null)
                {
                    text += $"(内部) " + e.InnerException;
                }
                else
                {
                    text += e.ToString();
                }
                text += $"\n语句：{command}";
                Log.Error(text);
                return;
            }
            finally
            {
                DatabaseController.Return(con);
            }
        }
    }

    //有回复请求，用于Query
    public class QueryCommandRequest : CommandRequest
    {
        public QueryCommandRequest(string table, Dictionary<string, object> filter, bool insert, params string[] columns)
            : base(table, RequestType.Query, null, filter)
        {
            Columns = columns;
            Insert_If_Does_Not_Exist = insert;
        }

        public Dictionary<string, object> Insert_Keys { get; set; }
        public string[] Columns { get; }
        public bool Insert_If_Does_Not_Exist { get; }

        private async Task<List<Dictionary<string, object>>> InternalGetResponseResult(DBConnection con, int failedtime = 0)
        {
            List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
            if (Columns.Length == 0)
                return result;
            string list = string.Empty;
            foreach (var key in Columns)
                list += $"`{key}`, ";
            list = list.Remove(list.Length - 2);
            string filter = string.Empty;
            if (Filter != null)
            {
                foreach (var f in Filter)
                    filter += $"`{f.Key}` = '{(f.Value is bool b ? (b ? "1" : "0") : f.Value.ToString().Replace("'", "\\'"))}' AND ";
                if (filter != String.Empty)
                    filter = filter.Remove(filter.Length - 4);
            }
            string command = $"SELECT {list} FROM `{Table}` {(filter != String.Empty ? $"WHERE {filter}" : "")}";
            try
            {
                MySqlCommand cmd = new MySqlCommand(command, con.Connection);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Dictionary<string, object> set = new Dictionary<string, object>();
                        foreach (var col in Columns)
                        {
                            set.Add(col, reader.GetValue(reader.GetOrdinal(col)));
                        }
                        result.Add(set);
                    }
                    reader.Close();
                }
                if (result.Count == 0 && Insert_If_Does_Not_Exist)
                {
                    Log.Info("查询请求时无符合条件，将插入值");
                    NoResponseCommandRequest req = new NoResponseCommandRequest(Table, RequestType.Insert, Insert_Keys ?? new Dictionary<string, object>(), null);
                    await req.ProcessNonQuery();
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Dictionary<string, object> set = new Dictionary<string, object>();
                            foreach (var col in Columns)
                            {
                                set.Add(col, reader.GetValue(reader.GetOrdinal(col)));
                            }
                            result.Add(set);
                        }
                        reader.Close();
                    }
                }
                cmd.Dispose();
                return result;
            }
            catch (Exception e)
            {
                if (e.InnerException != null)
                {
                    if (e.InnerException is IOException)
                    {
                        Log.Warn("执行查询类数据库请求捕获已知异常：与数据库的连接意外断开\n将重新建立连接并重新查询");
                        con.Connection.Close();
                        con.Connection.Open();
                        return null;
                    }
                    if (e is MySqlException)
                    {
                        failedtime++;
                        if (failedtime == 4)
                        {
                            Log.Error("出现MYSQL错误:" + e.Message);
                        }
                        else
                        {
                            Log.Warn("出现了Mysql数据库错误，重置连接并尝试：" + failedtime);
                            con.Connection.Close();
                            con.Connection.Open();
                            await InternalGetResponseResult(con);
                        }
                    }
                }
                string text = $"执行查询数据库请求时出现错误：\n";
                if (e.InnerException != null)
                {
                    text += $"(内部) " + e.InnerException;
                }
                else
                {
                    text += e.ToString();
                }
                text += $"\n语句：{command}";
                Log.Error(text);
                return new List<Dictionary<string, object>>();
            }
        }

        public async Task<List<Dictionary<string, object>>> GetResponseResult()
        {
            var con = await DatabaseController.Rent();
            List<Dictionary<string, object>> result;
            int times = 0;
            do
            {
                result = await InternalGetResponseResult(con);
                times++;
            }
            while (result.Count == 0 && Insert_If_Does_Not_Exist && times <= 3);
            DatabaseController.Return(con);
            return result;
        }
    }

    public abstract class CommandRequest
    {
        public CommandRequest(string table, RequestType type, Dictionary<string, object> keys, Dictionary<string, object> filter)
        {
            RequestType = type;
            Table = table;
            Keys = keys;
            Filter = filter;
        }

        public string Table { get; }
        public RequestType RequestType { get; }
        public Dictionary<string, object> Keys { get; }
        public Dictionary<string, object> Filter { get; }
    }

    public class DBConnection
    {
        public DBConnection(MySqlConnection con)
        {
            Connection = con;
            LastOperation = DateTime.Now;
            Occuiped = false;
        }

        public MySqlConnection Connection { get; }
        public bool Occuiped { get; set; }
        public DateTime LastOperation { get; set; }
    }

    public static class DatabaseController
    {
        public static Thread KeepAliveThread { get; private set; }
        public static List<DBConnection> Connections { get; private set; } = new List<DBConnection>();
        private static string ConnectionString { get; set; }
        public static bool DatabaseDisabled = false;
        public static object _lock = new object();
        private const int MaxPool = 7;
        private const int MinPool = 0;
        private const int IdleTimeout = 30;
        private const int HeartbeatTime = 300;

        public static void Init()
        {
            MySqlConnectionStringBuilder b = new MySqlConnectionStringBuilder();
            var info = RgCore.instance.Config.Database;
            b.Database = info.Database;
            b.UserID = info.Username;
            b.Password = info.Password;
            b.Port = info.Port;
            b.Server = info.IpAddress;
            ConnectionString = b.ToString();
            for (int i = 0; i < MaxPool; i++)
            {
                try
                {
                    MySqlConnection con = new MySqlConnection(ConnectionString);
                    con.Open();
                    Connections.Add(new DBConnection(con));
                }
                catch (Exception e)
                {
                    Log.Error("未能连接至数据库，数据库系统将不被启用：" + e);
                    DatabaseDisabled = true;
                    return;
                }
            }
            Thread a = new Thread(Update)
            {
                Name = $"Helper Thread"
            };
            KeepAliveThread = a;
            a.Start();
        }

        private static void Update()
        {
            while (true)
            {
                try
                {
                    for (int i = 0; i < Connections.Count; i++)
                    {
                        var con = Connections[i];
                        if (!con.Occuiped && con.Connection.State == System.Data.ConnectionState.Open && con.LastOperation.AddSeconds(IdleTimeout) < DateTime.Now)
                        {
                            if (i <= MinPool - 1)
                            {
                                if (con.LastOperation.AddSeconds(HeartbeatTime) < DateTime.Now)
                                {
                                    con.LastOperation = DateTime.Now;
                                    Log.SendRaw($"[MySQL 数据库] 连接#{i + 1} 长时间没有数据传输，发送心跳包维持连接", ConsoleColor.Blue);
                                    if (con.Connection.State == System.Data.ConnectionState.Closed)
                                    {
                                        con.Connection.Open();
                                    }
                                    MySqlCommand cmd = new MySqlCommand("show tables");
                                    cmd.ExecuteNonQuery();
                                }
                            }
                            else
                            {
                                con.Connection.Close();
                                con.Occuiped = false;
                                Log.SendRaw($"[MySQL 数据库] 连接#{i + 1} 长时间没有数据传输，已断开连接", ConsoleColor.Yellow);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Log.Error($"{Thread.CurrentThread.Name}线程错误：" + e);
                    continue;
                }
                Thread.Sleep(1000);
            }
        }

        public static async Task<DBConnection> Rent()
        {
            DBConnection con;
            lock (_lock)
            {
                while (true)
                {
                    var list = Connections.Where(p => !p.Occuiped);
                    if (list.Any())
                    {
                        con = list.First();
                        con.Occuiped = true;
                        break;
                    }
                    Thread.Sleep(10);
                }
            }
            if (con.Connection.State == System.Data.ConnectionState.Closed)
            {
                await con.Connection.OpenAsync();
            }
            return con;
        }

        public static void Return(DBConnection con)
        {
            con.LastOperation = DateTime.Now;
            con.Occuiped = false;
        }
    }
}